package edu.cmu.cs.webapp.ioms.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.eclipse.emf.common.util.EList;

import edu.cmu.cs.webapp.ioms.dao.DataAccessException;
import edu.cmu.cs.webapp.ioms.dao.LineItemDAO;
import edu.cmu.cs.webapp.ioms.models.IOMSPackage;
import edu.cmu.cs.webapp.ioms.models.Ingredient;
import edu.cmu.cs.webapp.ioms.models.LineItem;

import edu.cmu.cs.webapp.ioms.models.Product;
import edu.cmu.cs.webapp.ioms.models.PurchaseOrder;

public class LineItemDAOImpl extends DAO implements LineItemDAO{

	public LineItemDAOImpl() {
		// TODO Auto-generated constructor stub
		super();
	}
	
	public List<LineItem> getAllItem() throws DataAccessException{
		Connection con = null;
		LineItem item = null;
		PurchaseOrder po = null;
		Product prod = null;
        List<LineItem> itemlst = new ArrayList<LineItem>();
        try {
        	con = getConnection();
        	Statement stmt = con.createStatement();
        	String sql = "select * from item";
        	ResultSet rs = stmt.executeQuery(sql);
        	while(rs.next()){
        		item = IOMSPackage.eINSTANCE.getIOMSFactory().createLineItem();
        		po = IOMSPackage.eINSTANCE.getIOMSFactory().createPurchaseOrder();
        		prod = IOMSPackage.eINSTANCE.getIOMSFactory().createProduct();
        		
        		item.setItem_id(rs.getInt("item_id"));
        		po.setOrder_id(rs.getInt("order_id"));
        		item.setOrder(po);
        		
        		prod.setProd_id(rs.getInt("prod_id"));
        		item.setProduct(prod);
        		
        		item.setQuantity(rs.getInt("quantity"));
        		item.setUnit(rs.getString("unit"));
        		item.setRemark(rs.getString("remark"));
        		
        		itemlst.add(item);
        	}
            stmt.close();
            con.close();    	
        } catch (Exception e) {
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DataAccessException(e);
        }
        return itemlst;
	}

	@Override
	public PurchaseOrder getOrderItems(PurchaseOrder po) 
			throws DataAccessException {
		// TODO Auto-generated method stub
		Connection con = null;
		LineItem item = null;
		Product prod = null;
        List<LineItem> itemlst = new ArrayList<LineItem>();
        try {
        	con = getConnection();
        	Statement stmt = con.createStatement();
        	String sql = "select * from item where order_id=" + po.getOrder_id();
        	ResultSet rs = stmt.executeQuery(sql);
        	while(rs.next()){
        		item = IOMSPackage.eINSTANCE.getIOMSFactory().createLineItem();
           		prod = IOMSPackage.eINSTANCE.getIOMSFactory().createProduct();
        		
        		item.setItem_id(rs.getInt("item_id"));
        		item.setOrder(po);
        		
        		prod.setProd_id(rs.getInt("prod_id"));
        		item.setProduct(prod);
        		
        		item.setQuantity(rs.getInt("quantity"));
        		item.setUnit(rs.getString("unit"));
        		item.setRemark(rs.getString("remark"));
        		
        		itemlst.add(item);
        	}
        	po.getItems().addAll(itemlst);
            stmt.close();
            con.close();    	
        } catch (Exception e) {
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DataAccessException(e);
        }
        return po;
	}

	@Override
	public boolean addItem(LineItem item) throws DataAccessException {
		// TODO Auto-generated method stub
		Connection con = null;

		try {
			con = getConnection();
			
            PreparedStatement pstmt = con.prepareStatement(
            "insert into item(prod_id, order_id, quantity, unit, remark) " +
            "values (? ,?, ?, ?, ?)");
            
            pstmt.setInt(1, item.getProduct().getProd_id());
            pstmt.setInt(2, item.getOrder().getOrder_id());
            pstmt.setDouble(3, item.getQuantity());
            pstmt.setString(4, item.getUnit());
            pstmt.setString(5, item.getRemark());
            
            pstmt.executeUpdate();
            System.out.println("insert: " + item.toString());
			
			pstmt.close();
			con.close();
			
			return true;
		} catch (Exception e) {
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DataAccessException(e);
		}
	}

}
